using System;
using System.Collections.Generic;
using System.Text;
using OgilvyOne.IDAL;
using System.Collections;
using OgilvyOne.Model;
using System.Data.SqlClient;
using OgilvyOne.Utility;
using System.Data;

namespace OgilvyOne.SqlServerDAL
{
    public class PrivilegeKey : IPrivilegeKey
    {
        // Fields
        private const string PARM_CODE = "@Key_Code";
        private const string PARM_DESC = "@Key_Desc";
        private const string PARM_KEY_ID = "@Key_ID";
        private const string PARM_NAME = "@Key_Name";
        private const string PARM_VALUE = "@Key_Value";
        private const string SQL_DELETE = " update system_privilege_key set is_del='1' where key_uid=@Key_ID ";
        private const string SQL_INSERT = " exec p_save_privlKey  @Key_Name, @Key_Code, @Key_Desc, @Key_Value ";
        private const string SQL_SELECT_INFO = " select key_uid,key_name,key_code,key_desc,key_value from system_privilege_key where is_del='0' and key_uid=@Key_ID";
        private const string SQL_SELECT_LIST = " select key_uid,key_name,key_code,key_desc,key_value from system_privilege_key where is_del='0' order by  key_value ";
        private const string SQL_UPDATE = " exec p_update_privlKey  @Key_ID, @Key_Name, @Key_Code, @Key_Desc, @Key_Value ";

        // Methods
        public int Delete(int iKeyID)
        {
            SqlParameter parameter = new SqlParameter("@Key_ID", SqlDbType.Int, 4, "key_uid");
            parameter.Value = iKeyID;
            int num = 0;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        num = SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, " update system_privilege_key set is_del='1' where key_uid=@Key_ID ", new SqlParameter[] { parameter });
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        string sMethod = "OgilvyOne.SqlServerDAL.SysManage.PrivilegeKey.Delete(int iKeyID)";
                        EventsLog.WriteLog(exception, sMethod);
                    }
                    return num;
                }
            }
        }

        private static SqlParameter[] GetInsertParameters()
        {
            SqlParameter[] cachedParameters = SQLHelper.GetCachedParameters(" exec p_save_privlKey  @Key_Name, @Key_Code, @Key_Desc, @Key_Value ");
            if (cachedParameters == null)
            {
                cachedParameters = new SqlParameter[] { new SqlParameter("@Key_Name", SqlDbType.VarChar, 100), new SqlParameter("@Key_Code", SqlDbType.VarChar, 50), new SqlParameter("@Key_Desc", SqlDbType.VarChar, 300), new SqlParameter("@Key_Value", SqlDbType.Int, 4) };
                SQLHelper.CacheParameters(" exec p_save_privlKey  @Key_Name, @Key_Code, @Key_Desc, @Key_Value ", cachedParameters);
            }
            return cachedParameters;
        }

        public IList getKeyInfo(int iKeyID)
        {
            IList list = new ArrayList();
            try
            {
                SqlParameter parameter = new SqlParameter("@Key_ID", SqlDbType.Int, 4, "key_uid");
                parameter.Value = iKeyID;
                using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, " select key_uid,key_name,key_code,key_desc,key_value from system_privilege_key where is_del='0' and key_uid=@Key_ID", new SqlParameter[] { parameter }))
                {
                    while (reader.Read())
                    {
                        PrivilegeKeyInfo info = new PrivilegeKeyInfo();
                        info.keyID = reader.GetInt32(0);
                        info.KeyName = reader.GetString(1);
                        info.KeyCode = reader.GetString(2);
                        info.KeyDesc = reader.GetString(3);
                        info.KeyValue = reader.GetInt32(4);
                        list.Add(info);
                    }
                }
            }
            catch (Exception exception)
            {
                string sMethod = "OgilvyOne.SqlServerDAL.SysManage.Organization.getCurrNode(int iNode_ID)";
                EventsLog.WriteLog(exception, sMethod);
            }
            return list;
        }

        public IList getKeyList()
        {
            IList list = new ArrayList();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, " select key_uid,key_name,key_code,key_desc,key_value from system_privilege_key where is_del='0' order by  key_value ", null))
            {
                while (reader.Read())
                {
                    PrivilegeKeyInfo info = new PrivilegeKeyInfo();
                    info.keyID = reader.GetInt32(0);
                    info.KeyName = reader.GetString(1);
                    info.KeyCode = reader.GetString(2);
                    info.KeyDesc = reader.GetString(3);
                    info.KeyValue = reader.GetInt32(4);
                    list.Add(info);
                }
            }
            return list;
        }

        private static SqlParameter[] GetModifyParameters()
        {
            SqlParameter[] cachedParameters = SQLHelper.GetCachedParameters(" exec p_update_privlKey  @Key_ID, @Key_Name, @Key_Code, @Key_Desc, @Key_Value ");
            if (cachedParameters == null)
            {
                cachedParameters = new SqlParameter[] { new SqlParameter("@Key_ID", SqlDbType.Int, 4), new SqlParameter("@Key_Name", SqlDbType.VarChar, 100), new SqlParameter("@Key_Code", SqlDbType.VarChar, 50), new SqlParameter("@Key_Desc", SqlDbType.VarChar, 300), new SqlParameter("@Key_Value", SqlDbType.Int, 4) };
                SQLHelper.CacheParameters(" exec p_update_privlKey  @Key_ID, @Key_Name, @Key_Code, @Key_Desc, @Key_Value ", cachedParameters);
            }
            return cachedParameters;
        }

        public int Insert(PrivilegeKeyInfo keyInfo)
        {
            SqlParameter[] insertParameters = GetInsertParameters();
            this.SetInsertParameters(insertParameters, keyInfo);
            int num = 0;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        num = SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, " exec p_save_privlKey  @Key_Name, @Key_Code, @Key_Desc, @Key_Value ", insertParameters);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        string sMethod = "OgilvyOne.SqlServerDAL.SysManage.PrivilegeKey.Insert(PrivilegeKeyInfo keyInfo)";
                        EventsLog.WriteLog(exception, sMethod);
                    }
                    return num;
                }
            }
        }

        private void SetInsertParameters(SqlParameter[] parms, PrivilegeKeyInfo myKeyInfo)
        {
            parms[0].Value = myKeyInfo.KeyName;
            parms[1].Value = myKeyInfo.KeyCode;
            parms[2].Value = myKeyInfo.KeyDesc;
            parms[3].Value = myKeyInfo.KeyValue;
        }

        private void SetModifyParameters(SqlParameter[] parms, PrivilegeKeyInfo myKeyInfo)
        {
            parms[0].Value = myKeyInfo.keyID;
            parms[1].Value = myKeyInfo.KeyName;
            parms[2].Value = myKeyInfo.KeyCode;
            parms[3].Value = myKeyInfo.KeyDesc;
            parms[4].Value = myKeyInfo.KeyValue;
        }

        public int Update(PrivilegeKeyInfo keyInfo)
        {
            SqlParameter[] modifyParameters = GetModifyParameters();
            this.SetModifyParameters(modifyParameters, keyInfo);
            int num = 0;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        num = SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, " exec p_update_privlKey  @Key_ID, @Key_Name, @Key_Code, @Key_Desc, @Key_Value ", modifyParameters);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        string sMethod = "OgilvyOne.SqlServerDAL.SysManage.PrivilegeKey.Modify(PrivilegeKeyInfo keyInfo)";
                        EventsLog.WriteLog(exception, sMethod);
                    }
                    return num;
                }
            }
        }
    }
}
